package org.jsping.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.jsping.dao.IMusicDao;
import org.jsping.entity.Music;

public class MusicDaoImpl extends BaseDao implements IMusicDao {
	
	/*根据路径来拿到对应的歌曲信息*/
	@Override
	public Music getInfoByUrl(String murl) throws SQLException {
		Music music = null;
		String sql = "select `Mid`,Mname,Sname,`Time`,Murl,Lyric,Purl,Num from Music as m,SingerList as s where m.sid=s.sid and murl=?";
		ResultSet rs = this.executeQuery(sql, murl);
		try {
			//获取音乐的详细信息
			if (rs.next()) {
				music= new Music(rs.getInt("Mid"), rs.getString("Mname"), rs.getString("Sname"), rs.getInt("Time"),
						rs.getString("Murl"), rs.getString("Lyric"), rs.getString("Purl"), rs.getInt("Num"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return music;
	}

	/*根据歌曲id来拿到对应的歌曲信息*/
	@Override
	public Music getInfoByMid(int mid) throws SQLException {
		Music music = null;
		String sql = "select `Mid`,Mname,Sname,`Time`,Murl,Lyric,Purl,Num from Music as m,SingerList as s where m.sid=s.sid and mid=?";
		ResultSet rs = this.executeQuery(sql, mid);
		try {
			//获取音乐的详细信息
			if (rs.next()) {
				music= new Music(rs.getInt("Mid"), rs.getString("Mname"), rs.getString("Sname"), rs.getInt("Time"),
						rs.getString("Murl"), rs.getString("Lyric"), rs.getString("Purl"), rs.getInt("Num"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return music;
	}

	/**
	 * 获取一定数量的歌曲
	 */
	@Override
	public List<Music> getMusic(int num) throws SQLException {
		String sql = "select `Mid`,Mname,Sname,`Time`,Murl,Lyric,Purl,Num from Music as m,SingerList as s where m.sid=s.sid limit ?;";
		// 从数据库获取数据
		ResultSet rs = this.executeQuery(sql, num);
		List<Music> list = new ArrayList<>();
		// 生成list列表
		while (rs.next()) {
			Music m = new Music(rs.getInt("Mid"), rs.getString("Mname"), rs.getString("Sname"), rs.getInt("Time"),
					rs.getString("Murl"), rs.getString("Lyric"), rs.getString("Purl"), rs.getInt("Num"));
			list.add(m);
		}
		return list;
	}

	/**
	 * 获取一定数量的歌曲
	 */
	@Override
	public Map<String, Music> getMusics(int num) throws SQLException {
		String sql = "select `Mid`,Mname,Sname,`Time`,Murl,Lyric,Purl,Num from Music as m,SingerList as s where m.sid=s.sid limit ?";
		// 从数据库获取数据
		ResultSet rs = this.executeQuery(sql, num);
		Map<String,Music> musics = new HashMap<String,Music>();
		// 生成list列表
		while (rs.next()) {
			Music m = new Music(rs.getInt("Mid"), rs.getString("Mname"), rs.getString("Sname"), rs.getInt("Time"),
					rs.getString("Murl"), rs.getString("Lyric"), rs.getString("Purl"), rs.getInt("Num"));
			musics.put(String.valueOf(m.getMid()),m);
		}
		return musics;
	}

	/**
	 * 添加歌曲
	 */
	@Override
	public int addMusic(Music music, int sid) throws SQLException {
		String sql = "INSERT into Music(Mname,Sid,`Time`,Murl,Lyric,Purl) VALUES(?,?,?,?,?,?);";
		return this.executeUpdate(sql, music.getMname(), sid, music.getTime(), music.getMurl(), music.getLyric(),
				music.getPurl());
	}
	
	/**
	 * 查找歌曲是否存在
	 */
	@Override
	public String selectMusic(String mName, int sid) throws SQLException {
		String sql = "select Murl from Music where Mname = ? and Sid = ?";
		ResultSet rs = this.executeQuery(sql, mName, sid);
		if(rs.next()) {
			return rs.getString("Murl");
		}
		return null;
	}

	/**
	 * 根据歌曲id来更新歌曲信息
	 */
	@Override
	public int updateMusic(int mid, String murl) {
		String sql = "update Music set Murl = ? where mid = ?";
		try {
			return this.executeUpdate(sql, murl,mid);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.releaseAll();
		}
		return 0;
	}

	/**
	 * 获取数据库中歌曲的总数量
	 */
	@Override
	public int getCount() {
		int result = 0;
		ResultSet rs = null;
		try {
			String sql = " select count(*) from music";
			rs = executeQuery(sql, null);
			if(rs.next()) {
				result = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			releaseAll();
		}
		return result;
	}

	/**
	 * 获取指定歌曲信息
	 */
	@Override
	public Music getSpecialMusic(int num) {
		Music music = null;
		try {
			String sql = "SELECT `Mid`,Mname,Sname,`Time`,Murl,Lyric,Purl,Num FROM Music AS m,SingerList AS s WHERE m.sid=s.sid ORDER BY MID LIMIT ?,1";
			rs = executeQuery(sql, num);
			if (rs.next()) {
				music= new Music(rs.getInt("Mid"), rs.getString("Mname"), rs.getString("Sname"), rs.getInt("Time"),
						rs.getString("Murl"), rs.getString("Lyric"), rs.getString("Purl"), rs.getInt("Num"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return music;
	}

	/**
	 * 获取指定顺序的多少首歌曲
	 */
	@Override
	public Map<String, Music> getOrderMusics(int start, int num) {
		Map<String, Music> musics = null;
		try {
			String sql = "SELECT `Mid`,Mname,Sname,`Time`,Murl,Lyric,Purl,Num FROM Music AS m,SingerList AS s WHERE m.sid=s.sid ORDER BY MID LIMIT ?,?";
			rs = executeQuery(sql, start,num);
			if(rs!=null) {
				musics = new HashMap<String, Music>();
				while(rs.next()) {
					Music music= new Music(rs.getInt("Mid"), rs.getString("Mname"), rs.getString("Sname"), rs.getInt("Time"),
							rs.getString("Murl"), rs.getString("Lyric"), rs.getString("Purl"), rs.getInt("Num"));
					musics.put(String.valueOf(music.getMid()), music);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return musics;
	}

	//根据歌曲名和歌手id来返回对应的mid
	@Override
	public int getMid(String mname, int mid) throws SQLException {
		String sql = "select mid from Music where Mname = ? and Sid = ?";
		ResultSet rs = this.executeQuery(sql, mname, mid);
		if(rs.next()) {
			return rs.getInt(1);
		}
		return 0;
	}
}
